from datetime import datetime, timedelta

import pandas as pd
import numpy as np

from air_web.web_flask.tools.air_base import AirBase


class ACLoadExcute(AirBase):
    """空调负荷执行曲线接口:每个地市的基线/目标/实测空调负荷"""

    URL = "airConditionerLoadExecute"

    def get_same_tmp_date_list(self, region_list):
        """同温日规则：用最高温度，保留两位小数，浮动取绝对值最接近的三个"""
        sql = f"""select max(tmp) as tmp
                  from aclr_weather_hour
                  where datetime >= '{self.today_str}'
                    and datetime < DATE_ADD('{self.today_str}', INTERVAL 1 DAY)
                    and region_code in ('{"','".join(region_list)}')
               """
        df = self.sql_engine.query(sql)
        today_tmp = df['tmp'].tolist()[0]
        if today_tmp is None:
            self.log.warning("未查询到今日气温数据")
            return []
        today_tmp = round(today_tmp, 2)

        this_year = self.today_str[:4]
        last_year = int(this_year)-1
        sql = f"""select date(datetime) as data_date, max(tmp) as tmp
                  from aclr_weather_hour
                  #where datetime >= '{last_year}-01-01'
                  # and datetime < '{this_year}-01-01'
                   where datetime >='2024-07-29' and datetime < '2024-07-30'
                    and region_code in ('{"','".join(region_list)}')
                  group by date(datetime)
               """
        df = self.sql_engine.query(sql)
        if df.empty:
            self.log.warning("未查询到去年气温数据")
            return []

        df["tmp"] = df["tmp"].round(2)
        df['abs_diff'] = abs(df['tmp'] - today_tmp)
        closest_date_df = df.nsmallest(3, 'abs_diff')
        closest_date_df.sort_values("data_date", inplace=True)
        closest_date_df["data_date"] = pd.to_datetime(closest_date_df["data_date"]).dt.strftime(
            "%Y-%m-%d")
        self.log.debug(f"今日温度:{today_tmp},同温日:{closest_date_df.to_dict()}")

        same_tmp_date_list = closest_date_df["data_date"].tolist()
        return same_tmp_date_list

    def get_someday_data(self, on5, data_date):
        sql = f"""select org_no city_org_no,
                         org_name city_org_name,
                         date_format( data_time, '%%H:%%i' ) quarter,
                         p_kt_sum
                  from orgno_typeid_15min
                  where data_time >=  '{data_date}'  
                    and data_time <  DATE_ADD('{data_date}', INTERVAL 1 DAY)
                    and org_no = {on5} 
                    and type_id = 0 
               """
        df = self.sql_engine.query(sql)
        return df

    def get_on5_data(self, on5, ctrl_on5_dict, region_dict):
        if on5 in ctrl_on5_dict:  # 调控地市的基线取前一天
            baseline_day = (datetime.strptime(self.today_str, "%Y-%m-%d") - timedelta(
                days=1)).strftime("%Y-%m-%d")
            sql = f"""   
                    select b.*, a.real_load from
                    ((
                        select date_format( data_time, '%%H:%%i' ) quarter,
                               p_kt_sum real_load
                        from orgno_typeid_15min
                        where data_time >=  '{self.today_str}'  
                          and data_time <  DATE_ADD('{self.today_str}', INTERVAL 1 DAY)
                            and org_no = {on5} 
                            and type_id = 0 
                    ) a
                    right join (	
                        select org_no city_org_no,
                               org_name city_org_name,
                               date_format( data_time, '%%H:%%i' ) quarter,
                               p_kt_sum base_load
                        from orgno_typeid_15min
                        where data_time >=  DATE_SUB('{self.today_str}', INTERVAL 1 DAY)
                          and data_time <  '{self.today_str}' 
                          and org_no = {on5} 
                          and type_id = 0 
                    ) b on a.quarter = b.quarter) 
                    """
            data_df = self.sql_engine.query(sql)
            if data_df.empty:
                return data_df
            # if len(condition_control_df) > 5:
            #     condition_control_df = condition_control_df.sort_values(
            #         by="quarter"
            #     )
            #     condition_control_df[["base_load", "real_load"]] = (
            #         condition_control_df[["base_load", "real_load"]]
            #         .rolling(window=4)
            #         .mean()
            #         .bfill()
            #     )
            data_df["target_load"] = data_df["base_load"] * ctrl_on5_dict[on5]
            data_df["baseDay"] = baseline_day
        else:  # 非调控地市的基线取去年三个同温日的平均值
            data_df = self.get_someday_data(on5, self.today_str)
            #data_df = self.get_someday_data(on5,'2023-07-10')
            data_df.rename(columns={"p_kt_sum": "real_load"}, inplace=True)

            base_df = pd.DataFrame()
            if on5 not in region_dict:
                self.log.warning(f"未查询到该地市:{on5}的region_code")
                return pd.DataFrame()
            same_tmp_date_list = self.get_same_tmp_date_list(region_dict[on5])
            if len(same_tmp_date_list) == 0:
                return pd.DataFrame()
            merge_cols = ["city_org_no", "city_org_name", "quarter"]
            for data_date in same_tmp_date_list:
                df = self.get_someday_data(on5, data_date)
                #df = self.get_someday_data(on5,'2023-07-10')
                if df.empty:
                    continue
                if base_df.empty:
                    base_df = df
                else:
                    base_df = base_df.merge(df, on=merge_cols)
            if base_df.empty:
                data_df["base_load"] = None
            else:
                base_avg_df = base_df.set_index(merge_cols).mean(axis=1).reset_index()
                base_avg_df.rename(columns={0: "base_load"}, inplace=True)
                data_df = data_df.merge(
                    base_avg_df, on=["city_org_no", "city_org_name", "quarter"], how='right')
            data_df["target_load"] = 0
            data_df["baseDay"] = ','.join(same_tmp_date_list)
        data_df["day"] = self.today_str
        return data_df

    def get_region_code(self):
        sql = f"select distinct on5,region_code from aclr_base_doc_all"
        df = self.sql_engine.query(sql)
        region_dict = df.groupby('on5')['region_code'].agg(list).to_dict()
        return region_dict

    def get_ctrl_on5_dict(self):
        sql = f"""select c.org_no, 0.75 as target_prop
                  from city_approval_info c
                  join (select org_no from real_org_no where org_level=1 )r on r.org_no=c.org_no 
                  where control_time <= '{self.today_str}'
                  and end_time >= '{self.today_str}'
               """
        alert_df = self.sql_engine.query(sql)
        ctrl_on5_dict = alert_df.set_index('org_no')['target_prop'].to_dict()
        self.log.debug(f"ctrl_on5_dict:{ctrl_on5_dict}")
        return ctrl_on5_dict

    def get_on5_list(self):
        sql = "select org_no from real_org_no where org_level=1 "
        df = self.sql_engine.query(sql)
        on5_list = df["org_no"].tolist()
        return on5_list

    def get_ac_load_execute(self):
        on5_list = self.get_on5_list()
        ctrl_on5_dict = self.get_ctrl_on5_dict()
        region_dict = self.get_region_code()

        res_df = pd.DataFrame()
        for on5 in on5_list:
            df = self.get_on5_data(on5, ctrl_on5_dict, region_dict)
            if df.empty:
                self.log.warning(f"未查询到{on5}地市的数据")
            else:
                res_df = pd.concat([res_df, df])

        if res_df.empty:
            return []

        res_df["city_org_name"] = res_df["city_org_name"].str.replace("国网", "").str.replace(
            "供电公司", "").str.replace("新区", "")
        res_df = res_df.replace({np.nan: None})

        res = res_df.to_dict("records")
        return res

    def main(self):
        self.log.info("start #### air_condition_load_execute")
        ac_load_execute = self.get_ac_load_execute()
        self.log.debug(f"air_condition_load_execute_res:{ac_load_execute}")
        self.post_data(self.URL, ac_load_execute)
